Skip to main content

Common SQL Patterns

Subqueries are useful to collapse several data points into a single field. Below are some examples of common design patterns to make developing your own queries easier.

Rapid Platform utilises SQL Server.

Please note that Rapid refers to the current table as T. Often, you will need to ensure that you bind the results returned with your query, by checking them against an id field in the current table. This will be demonstrated in the examples below.

Because T is already in use, you cannot apply this letter as a label to any other tables within your query.

Avoid Comments

The example code in this section contains comments.

-- This is a comment

However, the subquery and computed fields do not support comments and will generate unknown errors if they are included. Please remove all comments from code, and test your code with the test fields before saving.

CONCAT( )

In this example, we will take the Invoices table and create a unique title field. CONCAT() joins together data into a single string, and requires at least two parameters, separated by a comma.

FieldTypeConfiguration
invoice_numberSingle Line of Text
contractor_idLookupLooks up to a contact with display field of Full Name
invoice_identifierSubquerySee Below

The goal of this query is to display the Invoice Identifier in the format {Contact Full Name}: INV{Invoice Number}. This can be achieved using:

SELECT CONCAT(C.first_name, ' ', C.last_name, ': INV', t.invoice_number)
FROM dbo.contact as C
WHERE C.id = t.contractor_id -- Ensures that only one item is returned
AND C.deleted IS NULL -- Ignore deleted data

LEFT( ) and RIGHT( )

In this example, we will take the Date of the Projects table, as well as the initials of the user that the Project is assigned to. We will then combine them to create a unique title field for the Projects table.

LEFT(input, number) retains a number of characters at the left of the string, and removes anything afterwards. This can be particularly useful for removing time zone data from a Date and Time column.

RIGHT(input, number) does the opposite: it retains the characters at the right of the string, and removes anything before the number of characters selected.

TableFieldTypeConfiguration
Projectsstart_dateDate and Time
Projectsassigned_to_idLookupStores the id of the principal user assigned to the project.
Principalsgiven_namesSingle Line of Text
PrincipalssurnameSingle Line of Text

The goal of this query is to first obtain information from another table: the first name and last name of the user that is assigned to the project.

We also want to remove all of the time data from the DateTime column, as it contains information tracking milliseconds and time zone, which we don't require. Finally, the query must concatenate all of this information into a single display string.

SELECT CONCAT
(
LEFT(p.[given_names], 1), -- Obtains only the first character of first name
LEFT(p.[surname], 1), -- Obtains only the first character of last name
': ',
LEFT(t.[start_date], 10) -- Take the first ten characters of the DateTime
-- (e.g. '2024-01-01' instead of '2024-08-01 02:00:00.0000000 +00:00')
)
FROM dbo.[principal] AS p
WHERE t.[assigned_to_id] = p.[id] -- Find the user ID of the user, using the lookup.
AND t.[deleted] IS NULL AND p.[deleted] IS NULL -- Ignore deleted data

This will now give us a column that displays the initials of the person responsible, and the start date, like so:

FL: 2024-08-01

FORMAT ( )

SQL Server produces DateTime fields like so: yyyy-MM-ddTHH:mm:ss.fffZ

An example would be 2024-08-01T02:00:00.000Z, which can be broken up into the date 2024-08-01, then time following the T (including milliseconds) 02:00:000, followed by time zone information Z, which means zero-time and indicates the data is based on UTC time.

SQL Server provides the format(value, format, [culture]) function to help us format this data.

Performance

In SQL Server, the FORMAT() function is very slow. Using this function on a will impact the performance of a table loading. It recommended instead to use CONVERT(), which will be outlined later in this article.

It is the combination of letters (glyphs) that represent the relevant date or time data.

A table of potential display formats is below:

Dates

ResultType
Days
d1 to 31Days
dd01 to 31Days, with single digits formatted as 01, 02, 03, etc.
dddMon to SunDay names, abbreviated
ddddMonday to SundayDay names, full
Months
m1 to 12Months
mm01 to 12Months, with single digits formatted as 01, 02, 03, etc.
mmmJan to DecMonth names, abbreviated
mmmmJanuary to DecemberMonth names, full
Years
y or yye.g. 24The current year, abbreviated
yyyye.g. 2024The current year, full

Times

ResultType
Meridiem
ttAM or PMTime of day, full
tA or PTime of day, abbreviated
Hours
H or HH00 to 24Military Hours
h1 to 12Standard Hours
hh01 to 12Standard Hours with 01, 02, 03, etc.
Minutes
mm00 to 59Minutes
Capitalisation

Glyphs are case-sensitive. mm is minutes, but MM is the month number.

For instance, if the date is 2024-08-01 we can use the following code to format it:

FORMAT(t.[start_date], 'dd-MM-yyyy')
-- 01-08-2024

FORMAT(t.[start_date], 'dd/MM/yyyy')
-- 01/08/2024

FORMAT(t.[start_date], 'HH:mm on dd/MM/yy')
-- 02:00 on 01/08/24

FORMAT(t.[start_date], 'htt on dd/MM/yy')
-- 2AM on 01/08/24

FORMAT(t.[start_date], 'ddd, d MMM `yy')
-- Thu, 1 Aug `24

FORMAT(t.[start_date], 'dddd, dd MMMM')
-- Thursday, 01 August

CONVERT( )

This is a much faster alternative to FORMAT(). It can be more difficult to produce the exact data desired, but not impossible. The general structure of this function is:

CONVERT(data_type(length), expression[, style])

So, when converting a Date and Time column's data, we could use the following query:

CONVERT(varchar(512), t.[start_date], 103)

varchar(512) declares the type of data we want to convert our result into, t.[start_date] is the data we want to convert, and 103 is the standard British/French date style.

You can read about all of the different date-time formats that available using CONVERT().

Further Reading